#coding:utf-8
#import pandas as pd
import pymysql
import pymysql.cursors
from urllib.request import urlopen, quote
#import sys
import numpy as np
import json
import random as rd
import os

class BaseInfo(object):
    def __init__(self, lat, lng, category):
        self.lat = lat
        self.lng = lng
        self.category = category#聚类的单个子类
class DistributionPointSet(object):
    def __init__(self, coordinate, addressName,all_load,all_volume,actual_volume):
        self.coordinate = coordinate
        self.addressName = addressName
        self.all_load = all_load#配送完后的剩余量
        self.all_volume=all_volume#配送点单天配送体积
        self.actual_volume=actual_volume#一趟的配送V
class pathBasicInfo(object):
    def __init__(self, path, distance):
        self.path = path#路径信息
        self.distance = distance#全程距离
class pathInfo(object):
    def __init__(self, orderPathArr, saveDistance,actualDistance):
        self.orderPathArr = orderPathArr
        self.saveDistance = saveDistance
        self.actualDistance = actualDistance
class DayInfo(object):
    def __init__(self, pathInfo, Daydistance):
        self.pathInfo = pathInfo#路径信息
        self.Daydistance = Daydistance#全程距离
def class_to_dict(obj):
    '''把对象(支持单个对象、list、set)转换成字典'''
    is_list = obj.__class__ == [].__class__
    is_set = obj.__class__ == set().__class__
     
    if is_list or is_set:
        obj_arr = []
        for o in obj:
            #把Object对象转换成Dict对象
            dict = {}
            dict.update(o.__dict__)
            obj_arr.append(dict)
        return obj_arr
    else:
        dict = {}
        dict.update(obj.__dict__)
        return dict
def connection():
    dbconn = pymysql.connect(
    host="127.0.0.1",
    database="test",
    user="root",
    password="",
    port=3306,
    charset='utf8'
    )
    return dbconn
def selet_deposit(f_place,s_place):
    db = connection()
    cursor = db.cursor()
    sql = "select dis from deposit where fir_place = '%s' and sec_place = '%s'" % (f_place,s_place)
    try:
        cursor.execute(sql)
        result = cursor.fetchall()[2][0]
        print(result)
    except:
        result = 0
    finally:
        db.close()
        print(result)
    return result
def chooseK(dataSet,i):
    list = []
    for j in range(1,i):
        cenList, clusterAssment = biKmeans(dataSet, j, distMeas=distEclud)
        sum0 = sum(clusterAssment[:,1])
        list.append(sum0)
    print(list)
    import matplotlib.pyplot as plt
    fig = plt.figure()
    ax = fig.add_subplot(111)
    ax.plot(list)
    plt.show()
def inset(fir_place,sec_place,fir_lat,fir_lng,sec_lat,sec_lng,dis):
    db = connection()
    cursor = db.cursor()
    sql = "insert into deposit (fir_place,sec_place,fir_lat,fir_lng,sec_lng,sec_lat,dis) value('%s','%s','%s','%s','%s','%s','%s')" % (fir_place,sec_place,fir_lat,fir_lng,sec_lng,sec_lat,dis)
    cursor.execute(sql)
    db.close()
def getDistance(deslat,deslng,originlat='40.012665',originlng='116.830302'):
    url='http://api.map.baidu.com/directionlite/v1/driving?origin='+str(originlat)+','+str(originlng)+'&destination='+str(deslat)+','+str(deslng)+'&ak=' #GET请求
    ak = 'ONLSAru40h6aLFic65fAXellBKzScaSQ'
    output = 'json'
    url2 = url+ak+'&output='+output
    print(url2)
    req = urlopen(url2)
    res = req.read().decode()
    temp = json.loads(res)
    if temp['status']==0 and temp['message']=='ok':
        return temp['result']['routes'][0]['distance']
    else:
        return "ak已超限或不正确"
k_count = 6 #聚类中心数量
startDate = '2017-06-02'
###查询数据库#### 
# 加上字符集参数，防止中文乱码
dbconn=connection()
# 执行sql语句
try:
    with dbconn.cursor() as cursor:
        sql = "select DISTINCT bourn from analysisdata_copy1_copy where createdate ='%s'" % startDate
        cursor.execute(sql)
        result = cursor.fetchall()

    with dbconn.cursor() as cursor:
        sql = "select bourn, lng,lat from address_copy1_copy where lng is not null and lat is not null"
        cursor.execute(sql)
        result_1 = cursor.fetchall()
finally:
    dbconn.close()
#将满足一车的数据进行直接配送 筛选剩余的配送点

x = []
y = []
locationArr=[]
for i in result:
    for j in result_1:
        if j[0] == i[0]:
            x.append(np.float64(j[1]).item())
            y.append(np.float64(j[2]).item())
            locationArr.append(i[0])
#####查询数据库end#####
x = np.array(x)
y = np.array(y)


headquarters_lat = "40.012665"
headquarters_lng = "116.830302"
SumPath=0
step=0
SumSavePath=0
pathBasicArr=[]

def distance(a, b):
    return (a[0] - b[0]) ** 2 + (a[1] - b[1]) ** 2
li
# K均值算法
def k_means(x, y, k_count):
    count = len(x)  # 点的个数
    # 随机选择K个点
    k = rd.sample(range(count), k_count)
    k_point = [[x[i], [y[i]]] for i in k]  # 保证有序
    k_point.sort()
    #global frames
    #global step
    while True:
        km = [[] for i in range(k_count)]  # 存储每个簇的索引
        # 遍历所有点
        for i in range(count):
            cp = [x[i], y[i]]  # 当前点
            # 计算cp点到所有质心的距离
            _sse = [distance(k_point[j], cp) for j in range(k_count)]
            # cp点到那个质心最近
            min_index = _sse.index(min(_sse))
            # 把cp点并入第i簇
            km[min_index].append(i)
        # 更换质心
        # step+=1
        k_new = []
        for i in range(k_count):
            _x = sum([x[j] for j in km[i]]) / len(km[i])
            _y = sum([y[j] for j in km[i]]) / len(km[i])
            k_new.append([_x, _y])
        k_new.sort()  # 排序
        if (k_new != k_point):  # 一直循环直到聚类中心没有变化
            k_point = k_new
        else:
            return k_point, km
#获取配送点-配送总体积
def getLoad(addressName):
    dbconn=connection()
    try:
        with dbconn.cursor() as cursor:
            sql = "SELECT SUM(`volume`) AS all_load from analysisdata_copy1_copy WHERE bourn=%s"
            cursor.execute(sql,addressName)
            data = cursor.fetchall()
    finally:
        dbconn.close()
    return data[0][0]
def getAllPathDistance(data):
    pathArr=[]
    global SumPath
    global step
    if len(data)==1:
        if selet_deposit((data[0])["addressName"],"总部") != 0:
            distance = selet_deposit((data[0])["addressName"],"总部")
        if selet_deposit((data[0])["addressName"],"总部") == 0:
            distance = getDistance((data[0])["coordinate"][0],(data[0])["coordinate"][1])
            inset((data[0])["addressName"],"总部",(data[0])["coordinate"][0],(data[0])["coordinate"][1],headquarters_lat,headquarters_lng,distance)
        SumPath+=(int(distance)*2)
        addressName=data[0]["addressName"]
        sets=DistributionPointSet([(data[0])["coordinate"][1],(data[0])["coordinate"][0]],addressName,0,(data[0])["all_volume"],(data[0])["all_load"])
        path=[]
        path.append(class_to_dict(sets))
        basic=pathBasicInfo(path,distance*2)
        pathBasicArr.append(class_to_dict(basic))#数据集处理
        print('171出车前往'+str(addressName)+'1次,此次出车合计'+str(distance*2))
        step+=1
    else:
        for i in range(len(data)-1):
            for j in range(i+1,len(data)):
                pointI=data[i]
                pointJ=data[j]
                if selet_deposit(pointI["addressName"],"总部") != 0:
                    distanceI = selet_deposit(pointI["addressName"],"总部")
                if selet_deposit(pointI["addressName"],"总部") == 0:
                    distanceI = getDistance(pointI["coordinate"][0],pointI["coordinate"][1])
                    inset(pointI["addressName"],"总部",pointI["coordinate"][0],pointI["coordinate"][1],headquarters_lat,headquarters_lng,distanceI)
                if selet_deposit(pointJ["addressName"],"总部") != 0:
                    distanceJ = selet_deposit(pointJ["addressName"],"总部")
                if selet_deposit(pointJ["addressName"],"总部") == 0:
                    distanceJ = getDistance(pointJ["coordinate"][0],pointJ["coordinate"][1])
                    inset(pointJ["addressName"],"总部",pointJ["coordinate"][0],pointJ["coordinate"][1],headquarters_lat,headquarters_lng,distanceI)
                if selet_deposit(pointI["addressName"],pointJ["addressName"]) != 0:
                    distanceIJ = selet_deposit(pointI["addressName"],pointJ["addressName"])
                if selet_deposit(pointI["addressName"],pointJ["addressName"]) == 0:
                    distanceIJ = getDistance(pointI["coordinate"][0],pointI["coordinate"][1],pointJ["coordinate"][0],pointJ["coordinate"][1])
                    inset(pointI["addressName"],pointJ["addressName"],pointI["coordinate"][0],pointI["coordinate"][1],pointJ["coordinate"][0],pointJ["coordinate"][1],distanceI)
                savePath=int(distanceI)+int(distanceJ)+int(distanceIJ)
                if savePath>0 and pointI["all_load"]+pointJ["all_load"]<=40:
                    actualPath=int(distanceI)+int(distanceJ)+int(distanceIJ)
                    orderPathArr=[]
                    orderPathArr.append(i)
                    orderPathArr.append(j)
                    path=pathInfo(orderPathArr,savePath,actualPath)
                    pathArr.append(path)
    return sorted(pathArr, key=lambda pathInfo: pathInfo.saveDistance)

arr = []
arr = k_means(x, y, k_count)
result = []
print(arr[1])
for i in range(k_count):
# for i in range(0,1):
    lng = arr[0][i][0]
    lat = arr[0][i][1]
    Clu_class = []
    sumVolume=0
    for j in arr[1][i]:
        addressName=locationArr[j]
        all_load=getLoad(addressName)
        all_volume=all_load
        if float(all_load)>=40:
            if selet_deposit(addressName,"总部") != 0:
                distance = selet_deposit(addressName,"总部")
            if selet_deposit(addressName,"总部") == 0:
                distance = getDistance(y[j],x[j])*2
                inset(addressName,"总部",y[j],x[j],headquarters_lat,headquarters_lng,distance)
            times=all_load/40
            for t in range(1,int(times)+1):
                leave_V=all_load-40*int(t)
                Sets=DistributionPointSet([x[j], y[j]],addressName,leave_V,all_volume,50)
                basic=pathBasicInfo(class_to_dict(Sets),distance)
                pathBasicArr.append(class_to_dict(basic))#数据集处理
            all_load=all_load-40*int(times)
            SumPath+=(int(distance)*int(times))
            print('218出车前往'+str(addressName)+str(int(times))+'次数,此次出车合计'+str(distance*int(times)))
            step+=int(times)
        sumVolume+=all_load
        if all_load!=0:
            Sets=DistributionPointSet([y[j], x[j]],addressName,all_load,all_volume,0)
        Clu_class.append(class_to_dict(Sets))
    pathArr=[]
    pathArr= getAllPathDistance(Clu_class)
    if len(pathArr)>0:
        unOrderPathPointArr=[]
        for i in range(len(pathArr)):
            i=len(pathArr)-i-1

            dataArr=[]
            dataArr=(pathArr[i]).orderPathArr
            flag=1
            for v in dataArr:
                if v in unOrderPathPointArr:
                    flag=0
                    break
            if flag==1:
                addressName=(Clu_class[dataArr[0]])["addressName"]
                actual_volume=(Clu_class[dataArr[0]])["all_load"]
                all_volume=(Clu_class[dataArr[0]])["all_volume"]
                addressName_1=(Clu_class[dataArr[1]])["addressName"]   
                actual_volume_1=(Clu_class[dataArr[1]])["all_load"]
                all_volume_1=(Clu_class[dataArr[1]])["all_volume"] 
                saveDistance=(pathArr[i]).saveDistance
                SumSavePath+=saveDistance
                SumPath+=int((pathArr[i]).actualDistance)
                step+=1
                print('251出车前往'+str(addressName)+'和'+str(addressName_1)+'1次,此次出车合计'+str((pathArr[i]).actualDistance))
                Sets=DistributionPointSet([Clu_class[dataArr[0]]["coordinate"][1],Clu_class[dataArr[0]]["coordinate"][0]],addressName,0,all_volume,actual_volume)
                Sets_1=DistributionPointSet([Clu_class[dataArr[1]]["coordinate"][1],Clu_class[dataArr[1]]["coordinate"][0]],addressName_1,0,all_volume_1,actual_volume_1)
                path=[]
                path.append(class_to_dict(Sets))
                path.append(class_to_dict(Sets_1))
                basic=pathBasicInfo(path,(pathArr[i]).actualDistance)
                pathBasicArr.append(class_to_dict(basic))#数据集处理
                for value in dataArr:
                    unOrderPathPointArr.append(value)
                unOrderPathPointArr=list(set(unOrderPathPointArr))
        for val in range(len(Clu_class)):
            if val not in unOrderPathPointArr:
                if selet_deposit((Clu_class[int(val)])["addressName"],"总部") != 0:
                    distance = selet_deposit((Clu_class[int(val)])["addressName"],"总部")
                if selet_deposit((Clu_class[int(val)])["addressName"],"总部") == 0:
                    distance = getDistance((Clu_class[int(val)])["coordinate"][0],(Clu_class[int(val)])["coordinate"][1])
                    inset((Clu_class[int(val)])["addressName"],"总部",(Clu_class[int(val)])["coordinate"][1],(Clu_class[int(val)])["coordinate"][0],headquarters_lng,headquarters_lat,distance)
                SumPath+=(2*int(distance))
                addressName=(Clu_class[int(val)])["addressName"]
                print('269出车前往'+str(addressName)+'1次,此次出车合计'+str(2*distance))
                sets=DistributionPointSet([(Clu_class[int(val)])["coordinate"][1],(Clu_class[int(val)])["coordinate"][0]],addressName,0,(Clu_class[int(val)])["all_volume"],(Clu_class[int(val)])["all_load"])
                path=[]
                path.append(class_to_dict(sets))
                basic=pathBasicInfo(path,distance*2)
                pathBasicArr.append(class_to_dict(basic))#数据集处理
                step+=1
    data = BaseInfo(lat,lng,Clu_class)
    result.append(class_to_dict(data))
Final_res=DayInfo(pathBasicArr,SumPath)
jsons={
    "code": 0,
    "msg": "Success",
    "data":None}
jsons["data"]=[class_to_dict(Final_res)]
path = os.getcwd()
file_name ='%s\public\static\json\%s-%s.json' % (path,'new',startDate.replace('-', ''))
fp = open(file_name,'w+',encoding="utf-8")
fp.write(json.dumps(jsons,sort_keys=True, indent=4, separators=(', ', ': '),ensure_ascii=False))
print([class_to_dict(Final_res)])
print('合计总里程'+str(SumPath))
print("出车次数："+str(step))


